﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class InvPalletDA : IInvPalletDA
    {
        private string BILLING_CASE = "@BILLING_CASE";
        private string BILLING_PALLET = "@BILLING_PALLET";
        private string CASE = "@CASE";
        private string CREATED_BY = "@CREATED_BY";
        private string DOC_ID = "@DOC_ID";
        private string DOC_TYPE = "@DOC_TYPE";
        private string EXEC_DATE = "@EXEC_DATE";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LINE_ID = "@LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PALLET = "@PALLET";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_INV_PALLET_UNIQUE = " SELECT TOP 1 DOC_ID FROM @_@INV_PALLET WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE  ";
        private string SQL_DELETE_INV_PALLET = " DELETE FROM @_@INV_PALLET WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE ";
        private string SQL_INSERT_INV_PALLET = "INSERT INTO @_@INV_PALLET(WH_ID,DOC_ID,DOC_TYPE,LINE_ID,OWNER_ID,SKU_ID,[CASE],PALLET,BILLING_CASE,BILLING_PALLET,EXEC_DATE,REMARK,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE)VALUES(@WH_ID,@DOC_ID,@DOC_TYPE,@LINE_ID,@OWNER_ID,@SKU_ID,@CASE,@PALLET,@BILLING_CASE,@BILLING_PALLET,@EXEC_DATE,@REMARK,@CREATED_BY,GETDATE(),@UPDATED_BY,GETDATE())";
        private string SQL_SELECT_ALL_INV_PALLET = "SELECT ROWID,WH_ID,DOC_ID,DOC_TYPE,LINE_ID,OWNER_ID,SKU_ID,[CASE],PALLET,BILLING_CASE,BILLING_PALLET,CONVERT(VARCHAR(30),EXEC_DATE ,23) AS EXEC_DATE,REMARK,TS,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE FROM @_@INV_PALLET ";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckInvPalletUnique(string docID, string docType, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType) };
            return (DBHelper.ExecuteDataSet(CommandType.Text, this.SQL_CHECK_INV_PALLET_UNIQUE.Replace("@_@", whLoginID), paras).Tables[0].Rows.Count > 0);
        }

        public bool CheckInvPalletUnique(DataBase dataBase, DbTransaction tran, string docID, string docType, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType) };
            return (dataBase.ExecuteDataSet(tran, CommandType.Text, this.SQL_CHECK_INV_PALLET_UNIQUE.Replace("@_@", whLoginID), paras).Tables[0].Rows.Count > 0);
        }

        public int DeleteInvPallet(string docID, string docType, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INV_PALLET.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteInvPallet(DataBase dataBase, DbTransaction tran, string docID, string docType, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INV_PALLET.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllInvPallet(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_PALLET.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllInvPallet(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_PALLET.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<InvPalletInfo> GetDetailByIDAndType(string docID, string docType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            if (docType == "ASN")
            {
                sql = "SELECT DISTINCT WH_ID,'ASN' AS DOC_TYPE,RECEIPT_ID AS DOC_ID,OWNER_ID,SKU_ID, CONVERT(VARCHAR(30),GETDATE() ,23) AS EXEC_DATE,0 AS [CASE],0 AS PALLET,0 AS BILLING_CASE,0 AS BILLING_PALLET,CONVERT(timestamp,0) AS TS, NULL AS CREATED_BY,NULL AS CREATED_DATE,NULL AS UPDATED_BY,NULL AS UPDATED_DATE,NULL AS REMARK FROM @_@RECEIPT_DETAIL WHERE RECEIPT_ID =  @RECEIPT_ID";
                sql = sql.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", docID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            }
            else
            {
                sql = "SELECT DISTINCT WH_ID,'SO' AS DOC_TYPE,ORDER_ID AS DOC_ID,OWNER_ID,SKU_ID, CONVERT(VARCHAR(30),GETDATE() ,23) AS EXEC_DATE,0 AS [CASE],0 AS PALLET,0 AS BILLING_CASE,0 AS BILLING_PALLET,CONVERT(timestamp,0) AS TS, NULL AS CREATED_BY,NULL AS CREATED_DATE,NULL AS UPDATED_BY,NULL AS UPDATED_DATE,NULL AS REMARK FROM @_@ORDER_DETAIL WHERE ORDER_ID =  @ORDER_ID";
                sql = sql.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", docID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            }
            List<InvPalletInfo> invPalletListInfo = new List<InvPalletInfo>();
            InvPalletInfo invPalletInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00003;
                    invPalletInfo = new InvPalletInfo(0, reader["WH_ID"].ToString(), reader["DOC_ID"].ToString(), reader["DOC_TYPE"].ToString(), 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE"].ToString() != "") ? int.Parse(reader["CASE"].ToString()) : 0, (reader["PALLET"].ToString() != "") ? int.Parse(reader["PALLET"].ToString()) : 0, (reader["BILLING_CASE"].ToString() != "") ? int.Parse(reader["BILLING_CASE"].ToString()) : 0, (reader["BILLING_PALLET"].ToString() != "") ? int.Parse(reader["BILLING_PALLET"].ToString()) : 0, reader["EXEC_DATE"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()));
                    invPalletListInfo.Add(invPalletInfo);
                }
            }
            return invPalletListInfo;
        }

        public List<InvPalletInfo> GetInvPalletByIDAndType(string docID, string docType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_PALLET.Replace("@_@", whLoginID) + " WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<InvPalletInfo> invPalletListInfo = new List<InvPalletInfo>();
            InvPalletInfo invPalletInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    invPalletInfo = new InvPalletInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DOC_ID"].ToString(), reader["DOC_TYPE"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE"].ToString() != "") ? decimal.Parse(reader["CASE"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["BILLING_CASE"].ToString() != "") ? decimal.Parse(reader["BILLING_CASE"].ToString()) : 0M, (reader["BILLING_PALLET"].ToString() != "") ? decimal.Parse(reader["BILLING_PALLET"].ToString()) : 0M, reader["EXEC_DATE"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    invPalletListInfo.Add(invPalletInfo);
                }
            }
            return invPalletListInfo;
        }

        public List<InvPalletInfo> GetInvPalletByIDAndType(DataBase dataBase, DbTransaction tran, string docID, string docType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_PALLET.Replace("@_@", whLoginID) + " WHERE DOC_ID = @DOC_ID AND DOC_TYPE = @DOC_TYPE ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DOC_ID", docID), new SqlParameter("@DOC_TYPE", docType), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<InvPalletInfo> invPalletListInfo = new List<InvPalletInfo>();
            InvPalletInfo invPalletInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                invPalletInfo = new InvPalletInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DOC_ID"].ToString(), reader["DOC_TYPE"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE"].ToString() != "") ? decimal.Parse(reader["CASE"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["BILLING_CASE"].ToString() != "") ? decimal.Parse(reader["BILLING_CASE"].ToString()) : 0M, (reader["BILLING_PALLET"].ToString() != "") ? decimal.Parse(reader["BILLING_PALLET"].ToString()) : 0M, reader["EXEC_DATE"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                invPalletListInfo.Add(invPalletInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invPalletListInfo;
        }

        public int InsertInvPallet(InvPalletInfo invPalletInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                invPalletInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvPallet_Parameters(invPalletInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_PALLET.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertInvPallet(DataBase dataBase, DbTransaction tran, InvPalletInfo invPalletInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invPalletInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvPallet_Parameters(invPalletInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_PALLET.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_InvPallet_Parameters(InvPalletInfo invPalletInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DOC_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DOC_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CASE, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.BILLING_CASE, SqlDbType.Decimal, 13), new SqlParameter(this.BILLING_PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.EXEC_DATE, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 300), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30) };
            if (!string.IsNullOrEmpty(invPalletInfo.WhID))
            {
                paramArray[0].Value = invPalletInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = invPalletInfo.DocID;
            paramArray[2].Value = invPalletInfo.DocType;
            paramArray[3].Value = invPalletInfo.LineID;
            paramArray[4].Value = invPalletInfo.OwnerID;
            paramArray[5].Value = invPalletInfo.SkuID;
            paramArray[6].Value = invPalletInfo.Case;
            paramArray[7].Value = invPalletInfo.Pallet;
            paramArray[8].Value = invPalletInfo.BillingCase;
            paramArray[9].Value = invPalletInfo.BillingPallet;
            if (!string.IsNullOrEmpty(invPalletInfo.ExecDate))
            {
                paramArray[10].Value = invPalletInfo.ExecDate;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invPalletInfo.Remark))
            {
                paramArray[11].Value = invPalletInfo.Remark;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invPalletInfo.CreatedBy))
            {
                paramArray[12].Value = invPalletInfo.CreatedBy;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invPalletInfo.UpdatedBy))
            {
                paramArray[13].Value = invPalletInfo.UpdatedBy;
                return paramArray;
            }
            paramArray[13].Value = DBNull.Value;
            return paramArray;
        }
    }
}

